﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.XWPF.UserModel;
using org.in2bits.MyXls;


namespace CommunitySystem
{
    public class PublicOfficialSelectionBO
    {
        /// <summary>
        /// 查看职位未审核的有多少条--yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <returns></returns> 
        public string ChaKanShiFouDouShenHe(string OrgID, int year, string islocked)
        {
            string sql = "";
            if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
            {
                sql =
                    @"select count(p.[ID]) Count from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID where  p.status =1 and
                     (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) and p.year = " + year;
            }
            else
            {
                sql =
                    @"select count(p.[ID]) Count from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID where p.status =1 and p.year = " + year;
            }
            sql += " and p.IsLocked = '" + islocked + "'";
            //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
            //{
            //    sql += " and o.parentid = " + OrgID;
            //}
            //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
            //{
            //    sql += " and o.id = " + OrgID;
            //}

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else return null;
        }

        /// <summary>
        /// 查看报名人数未审核的有多少条
        /// </summary>
        /// <param name="OrgID"></param>
        /// <returns></returns>
        public string ChaKanShiFouDouShenHeBaoMingRen(int year, string islocked)
        {
            string sql =
                @"select COUNT(por.ID) Cou from PublicOfficialRecord por
inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
where ((GeRenStatus = 0 or GeRenStatus is null) and GeRenStatus != 4  and GeRenStatus != 3) and pos.year = " + year;
            sql += " and pos.IsLocked = '" + islocked + "'";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else return null;
        }

        public string GetOrgIDbyDengLuRen(int ID)
        {
            string sql = string.Format(@"select OrgCode from UserInfo where UserID =  {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else return null;
        }

        public Dictionary<string, int> GetTypebyOrgID(int ID)
        {
            Dictionary<string, int> dic = new Dictionary<string, int>();
            string sql = "";
            if (ID == 0)
            {
                dic.Add("", ID);
            }
            else
            {
                sql = string.Format(@"select type from OrgForHR where ID =  {0}", ID);
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    dic.Add(ds.Tables[0].Rows[0][0].ToString(), ID);
                }
                else dic = null;
            }
            return dic;
        }

        public string GetOrgNamebyOrgID(int ID)
        {
            string sql = string.Format(@"select Name from OrgForHR where ID =  {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else
                return null;
        }
        /// <summary>
        /// yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int GetPublicOfficialSelectionCount(string OrgID, int ID, int status, int year, string islocked, int ExameID)
        {
            string sql = "";
            if (ID == 0)
            {
                if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                {
                    sql =
                        @"select count(p.[ID])/20 + 1 Count from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID where 1=1 and p.year = " + year + @" and
                     (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")) ";
                    //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                    //{
                    //    sql += " and o.parentid = " + OrgID;
                    //}
                    //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                    //{
                    //    sql += " and o.id = " + OrgID;
                    //}
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                }
                else//admin登录的
                {
                    sql =
                        @"select count(p.[ID])/20 + 1 Count from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID where 1=1 and p.year = " +
                        year;
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                }
            }

            else//修改单个信息的时候用
            {
                sql = string.Format(@"select count(p.[ID])/20 + 1 Count from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID where p.ID = {0}", ID);
                if (status != -1)
                {
                    sql += " and p.status = " + status;
                }
            }
            sql += " and p.IsLocked = '" + islocked + "'";
            if (ExameID != 0)
                sql += " and p.ExameID = " + ExameID;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }
        /// <summary>
        /// 用于导出选调交流或竞争性选拔的单位
        /// </summary>
        /// <param name="tiaojian"></param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialSelectionModel> GetPublicOfficialSelectionForDaoChu(string tiaojian, int year, string islocked, int ExameID)
        {
            ObservableCollection<PublicOfficialSelectionModel> rights = new ObservableCollection<PublicOfficialSelectionModel>();
            string sql = "";
            sql =
                @"select distinct p.ID,p.*,o.Name OrgName from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID
inner join PublicOfficialRecord por on por.PublicOfficialSelectionID = p.ID where p.Status = 4 and por.GeRenStatus = 1 and p.year = " +
                year;
            if (tiaojian == "竞争性选拔")
            {
                sql += " and IsJZXXB  = 1";
            }
            else
            {
                sql += " and IsXDJL  = 1";
            }
            sql += " and p.IsLocked = '" + islocked + "'";
            if (ExameID != 0)
                sql += " and p.ExameID = " + ExameID;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialSelectionModel.FillEntity(dr));
            }
            return rights;
        }

        /// <summary>
        /// 获得登录人可见的申报的职位--yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialSelectionModel> GetPublicOfficialSelection(string OrgID, int ID, int start, int end, int status, int year, string islocked, int ExameID)
        {
            ObservableCollection<PublicOfficialSelectionModel> rights = new ObservableCollection<PublicOfficialSelectionModel>();
            string sql = "";
            if (ID == 0)
            {
                if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                {
                    sql =
                        @"select top " + (end - start) + @" ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID where p.ID not   in   
(select   top " + start + @" ID from PublicOfficialSelection ) and
                     (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")) and p.year = " + year;
                    //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                    //{
                    //    sql += " and o.parentid = " + OrgID;
                    //}
                    //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                    //{
                    //    sql += " and o.id = " + OrgID;
                    //}
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                    sql += " and p.IsLocked = '" + islocked + "'";
                    if (ExameID != 0)
                        sql += " and p.ExameID = " + ExameID;
                }
                else//admin登录的
                {
                    sql = @"select top " + (end - start) + @" ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID 
where p.ID not   in   
(select   top " + start + @" ID from PublicOfficialSelection )  and p.year = " + year;
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                    sql += " and p.IsLocked = '" + islocked + "'";
                    if (ExameID != 0)
                        sql += " and p.ExameID = " + ExameID;
                    sql += " order by p.ID";
                }
            }

            else//修改单个信息的时候用
            {
                sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID where p.ID = {0}", ID);
                if (status != -1)
                {
                    sql += " and p.status = " + status;
                }
                sql += " and p.IsLocked = '" + islocked + "'";
                if (ExameID != 0)
                    sql += " and p.ExameID = " + ExameID;
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialSelectionModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool AddPublicOfficialSelectionModel(PublicOfficialSelectionModel item)
        {
            string sql = string.Format(@"INSERT INTO PublicOfficialSelection
           ([OrgID]
           ,[Department]
           ,[Duty]
           ,[DutyDescription]
           ,[PeopleCount]
           ,[Education]
           ,[Political]
           ,[DutyQualifications]
           ,[Memo]
           ,[Status]
           ,[CheckMemo]
           ,[IsXDJL]
           ,[IsJZXXB],year,FaBuType,DutyLevel,DutyAttribute,IsLocked,Department_Duty)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,{9}
           ,'{10}'
           ,'{11}'
           ,'{12}','{13}','{14}','{15}','{16}','{17}','{18}')", item.OrgID, item.Department, item.Duty, item.DutyDescription
                   , item.PeopleCount, item.Education, item.Political, item.DutyQualifications
                   , item.Memo
                   , item.Status, item.CheckMemo, item.IsXDJL, item.IsJZXXB, item.Year, item.FaBuType, item.DutyLevel, item.DutyAttribute, item.IsLocked, item.Department_Duty);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdatePublicOfficialSelectionModel(PublicOfficialSelectionModel item)
        {
            string sql = string.Format(@"UPDATE PublicOfficialSelection
   SET [OrgID] = {0}
      ,[Department] = '{1}'
      ,[Duty] = '{2}'
      ,[DutyDescription] = '{3}'
      ,[PeopleCount] = '{4}'
      ,[Education] = '{5}'
      ,[Political] = '{6}'
      ,[DutyQualifications] = '{7}'
      ,[Memo] = '{8}'
      ,[Status] = '{9}'
      ,[IsXDJL] = '{10}'
      ,[IsJZXXB] = '{11}'
    ,year = '{12}',
    FaBuType = '{13}'
 ,DutyLevel = '{14}',
    DutyAttribute = '{15}',
IsLocked = '{16}',Department_Duty = '{17}'
 WHERE ID = {18}", item.OrgID, item.Department, item.Duty, item.DutyDescription
                   , item.PeopleCount, item.Education, item.Political, item.DutyQualifications
                   , item.Memo
                   , item.Status, item.IsXDJL, item.IsJZXXB, item.Year, item.FaBuType, item.DutyLevel, item.DutyAttribute, item.IsLocked, item.Department_Duty, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DetelePublicOfficialSelectionModel(string ID)
        {
            string sql = "delete from PublicOfficialSelection where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 确定保存
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool SavePublicOfficialSelectionModel(string ID)
        {
            string sql = "update dbo.PublicOfficialSelection set Status = 1 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 通过审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CheckPublicOfficialSelectionModel(string ID)
        {
            string sql = "update dbo.PublicOfficialSelection set Status = 3 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 退回
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool TuiPublicOfficialSelectionModel(string ID, string memo)
        {
            string sql = "update dbo.PublicOfficialSelection set Status = 2,CheckMemo = '" + memo + "' where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 发布
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool FaBuPublicOfficialSelectionModel(string ID, string fabutype)
        {
            string sql = "update dbo.PublicOfficialSelection set Status = 4,FaBuType = '" + fabutype + "' where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 取消审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CancelPublicOfficialSelectionModel(string ID)
        {
            string sql = "update dbo.PublicOfficialSelection set Status = 1 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        #region 报名情况
        public int GetPublicOfficialRecordCount(int OrgID, int year, string islocked, int ExameID)
        {
            string sql = "";
            if (OrgID != 0)
            {
                sql =
                    @"select COUNT( distinct pos.ID)/20 + 1 Count from PublicOfficialSelection pos
left join OrgForHR o on o.ID = pos.OrgID
left join PublicOfficialRecord por on por.PublicOfficialSelectionID = pos.ID
left join PublicOfficialInfo poi on poi.ID = por.ExameID  where pos.year = " + year + " and 1=1 ";
                if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                {
                    sql += " and o.parentid = " + OrgID;
                }
                if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                {
                    sql += " and o.id = " + OrgID;
                }
            }
            else//admin登录的
            {
                sql = @"select COUNT( distinct pos.ID)/20 + 1 Count from PublicOfficialSelection pos
left join OrgForHR o on o.ID = pos.OrgID
left join PublicOfficialRecord por on por.PublicOfficialSelectionID = pos.ID
left join PublicOfficialInfo poi on poi.ID = por.ExameID   where pos.year = " + year + " and 1=1 ";
            }
            sql += " and pos.IsLocked = '" + islocked + "'";
            if (ExameID != 0)
                sql += " and por.ExameID = " + ExameID;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }

        public ObservableCollection<PublicOfficialInfo> GetPublicOfficialInfo(int status,int year)
        {
            string sql = @"select distinct 0 as ID,'无' as Name from PublicOfficialInfo 
union all
select ID,Name from PublicOfficialInfo where status = " + status + " and YEAR(YM) = " + year;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            ObservableCollection<PublicOfficialInfo> models = new ObservableCollection<PublicOfficialInfo>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PublicOfficialInfo model = new PublicOfficialInfo();
                model.ID = Convert.ToInt32(dr["ID"]);
                model.Name = dr["Name"].ToString();
                models.Add(model);
            }
            return models;
        }

        /// <summary>
        /// 获得登录人可见的申报的职位
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialRecordModel> GetPublicOfficialRecord(int OrgID, int start, int end, int year, string islocked, int ExameID)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = "";

            if (OrgID != 0)
            {
                sql =
                    @"select top " + (end - start) + @" ROW_NUMBER() OVER(ORDER BY bb.ID ) Sec,  bb.ID,bb.* from
(select distinct  pos.ID,o.Name Unit,pos.Duty Duty,pos.Department,pos.Department_Duty,pos.Status,o.ID OrgID,o.ParentID,0 as GeRenStatus,por.Date,pos.ID as PublicOfficialSelectionID,0 as TotalScore,
'' as EmpName,'' as CardNo,'' as OrgName,'' as NowDuty,'' as BaoMingCount1,0 as PublicOfficialRecordID,0 as EmpID,0 as BiShiScore,0 as MianShiScore,'' as CheckMemo,pos.FaBuType,
case when aa.Cou is null then 0 else aa.Cou end BaoMingCount,0 as MingCi,'' IsTongYi,pos.IsLocked,
case when cc.Cou is null then 0 else cc.Cou end DaiShenHeCount,
case when dd.Cou is null then 0 else dd.Cou end YiShenHeCount,
case when ee.Cou is null then 0 else ee.Cou end GaiYiShenHeCount from PublicOfficialSelection pos
left join OrgForHR o on o.ID = pos.OrgID
left join PublicOfficialRecord por on por.PublicOfficialSelectionID = pos.ID
left join PublicOfficialInfo poi on poi.ID = por.ExameID 
left join 
(select COUNT(por.ID) Cou,PublicOfficialSelectionID from PublicOfficialRecord
por inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID=pos.ID 
where IsLocked = '" + islocked + "' and pos.year = " + year + @" and  
(GeRenStatus = 0 or GeRenStatus is null) and GeRenStatus != 3 and pos.Status = 4  group by PublicOfficialSelectionID) cc 
on cc.PublicOfficialSelectionID = pos.ID
left join 
(select COUNT(por.ID) Cou,PublicOfficialSelectionID from PublicOfficialRecord
por inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID=pos.ID
where IsLocked  = '" + islocked + "' and pos.year = " + year + @" and 
(GeRenStatus != 0 and GeRenStatus is not null) and GeRenStatus != 3 and pos.Status = 4  group by PublicOfficialSelectionID) dd 
on dd.PublicOfficialSelectionID = pos.ID
 left join
(select COUNT(por.ID) Cou,PublicOfficialSelectionID from PublicOfficialRecord
por inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID=pos.ID
where IsLocked  = '" + islocked + "' and pos.year = " + year + @" and  GeRenStatus != 3 and pos.Status = 4 group by PublicOfficialSelectionID)
aa on pos.ID = aa.PublicOfficialSelectionID
left join 
(select COUNT(por.ID) Cou,PublicOfficialSelectionID from PublicOfficialRecord
por inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID=pos.ID
where IsLocked = '" + islocked + "' and pos.year = " + year + @" and 
(GeRenStatus =1) and GeRenStatus != 3 and pos.Status = 4  group by PublicOfficialSelectionID) ee 
on ee.PublicOfficialSelectionID = pos.ID
 where pos.ID not   in   
(select   top " + start + @" ID from PublicOfficialSelection ) and pos.year = " + year + ") bb where bb.GeRenStatus != 3 and  bb.Status = 4 and 1=1  ";
                if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                {
                    sql += " and bb.parentid = " + OrgID;
                }
                if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                {
                    sql += " and bb.id = " + OrgID;
                }
                sql += " and bb.IsLocked = '" + islocked + "'";
                if (ExameID != 0)
                    sql += " and bb.ExameID = " + ExameID;
            }
            else//admin登录的
            {
                sql = @"select top " + (end - start) + @" ROW_NUMBER() OVER(ORDER BY bb.ID ) Sec, bb.ID,bb.* from
(select distinct pos.ID,o.Name Unit,pos.Duty Duty,pos.Department,pos.Department_Duty,pos.Status,o.ID OrgID,o.ParentID,0 as GeRenStatus,por.Date,pos.ID as PublicOfficialSelectionID,
'' as EmpName,'' as CardNo,'' as NowUnit,'' as NowDuty,'' as BaoMingCount1,0 as PublicOfficialRecordID,'' as CheckMemo,0 as EmpID,0 as BiShiScore,0 as MianShiScore,0 as TotalScore,
case when aa.Cou is null then 0 else aa.Cou end BaoMingCount,0 as MingCi,pos.FaBuType,'' IsTongYi,pos.IsLocked,
case when cc.Cou is null then 0 else cc.Cou end DaiShenHeCount,
case when dd.Cou is null then 0 else dd.Cou end YiShenHeCount,
case when ee.Cou is null then 0 else ee.Cou end GaiYiShenHeCount from PublicOfficialSelection pos
left join OrgForHR o on o.ID = pos.OrgID
left join PublicOfficialRecord por on por.PublicOfficialSelectionID = pos.ID
left join PublicOfficialInfo poi on poi.ID = por.ExameID 
left join 
(select COUNT(por.ID) Cou,PublicOfficialSelectionID from PublicOfficialRecord
por inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID=pos.ID 
where IsLocked  = '" + islocked + "' and pos.year = " + year + @" and  
(GeRenStatus = 0 or GeRenStatus is null) and GeRenStatus != 3 and pos.Status = 4  group by PublicOfficialSelectionID) cc 
on cc.PublicOfficialSelectionID = pos.ID
left join 
(select COUNT(por.ID) Cou,PublicOfficialSelectionID from PublicOfficialRecord
por inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID=pos.ID
where IsLocked = '" + islocked + "' and pos.year = " + year + @" and 
(GeRenStatus != 0 and GeRenStatus is not null) and GeRenStatus != 3 and pos.Status = 4  group by PublicOfficialSelectionID) dd 
on dd.PublicOfficialSelectionID = pos.ID
 left join
(select COUNT(por.ID) Cou,PublicOfficialSelectionID from PublicOfficialRecord
por inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID=pos.ID
where IsLocked  = '" + islocked + "' and pos.year = " + year + @" and  GeRenStatus != 3 and pos.Status = 4 group by PublicOfficialSelectionID)
aa on pos.ID = aa.PublicOfficialSelectionID
left join 
(select COUNT(por.ID) Cou,PublicOfficialSelectionID from PublicOfficialRecord
por inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID=pos.ID
where IsLocked = '" + islocked + "' and pos.year = " + year + @" and 
(GeRenStatus =1) and GeRenStatus != 3 and pos.Status = 4  group by PublicOfficialSelectionID) ee 
on ee.PublicOfficialSelectionID = pos.ID
 where pos.ID not   in   
(select   top " + start + @" ID from PublicOfficialSelection ) and pos.year = " + year + ") bb where bb.Status = 4 and  1=1  ";
                sql += " and bb.IsLocked = '" + islocked + "'";
                if (ExameID != 0)
                    sql += " and bb.ExameID = " + ExameID;
                sql += " order by bb.ID";
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 查看每个职位的报名人
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialRecordModel> GetPublicOfficialRecordbyID(int ID)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = "";

            sql =
                @"select ROW_NUMBER() OVER(ORDER BY aa.Unit,aa.Department_Duty,aa.Duty ) Sec,  aa.* from 
(select  distinct emp.ID EmpID,emp.Name EmpName,emp.CardNo,o.Name NowUnit,emp.PositionName NowDuty,por.id as PublicOfficialRecordID, 0 as DaiShenHeCount,0 as TotalScore
,aa.Cou BaoMingCount1,por.GeRenStatus,'' as Status,null as Date,oo.Name as Unit,pos.Department as Department,pos.Duty,pos.Department_Duty,PublicOfficialSelectionID,por.CheckMemo,0 as BaoMingCount,0 as ID,
0 as BiShiScore,0 as MianShiScore,pos.FaBuType,por.IsTongYi,pos.IsLocked,0 as MingCi
  from Emp_BasicInfo emp 
inner join PublicOfficialRecord por on emp.ID = por.EmpID
inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
inner join OrgForHR o on o.ID = emp.OrgID
inner join OrgForHR oo on oo.ID = pos.OrgID
inner join
(select COUNT(ID) Cou,EmpID from PublicOfficialRecord group by EmpID) aa on emp.ID = aa.EmpID) aa
 where aa.GeRenStatus != 3 and 1 = 1";
            if(ID != 0)
            {
                sql += " and aa.PublicOfficialSelectionID =" + ID;
            }
            sql += " order by aa.Unit,aa.Department_Duty,aa.Duty";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 导出岗位审核通过名单
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialRecordModel> GetPublicOfficial(int year, string islocked)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = "";

            sql =
                @"select ROW_NUMBER() OVER(ORDER BY aa.Department,aa.Department_Duty,aa.Duty ) Sec,  aa.* from 
(select  distinct emp.ID EmpID,emp.Name EmpName,emp.CardNo,o.Name OrgName,emp.PositionName,por.id as PublicOfficialRecordID
,aa.Cou BaoMingCount1,por.GeRenStatus,pos.Status as Status1,null as Date,oo.Name as Department,pos.Duty,pos.Department_Duty,PublicOfficialSelectionID,
pos.FaBuType,por.IsTongYi,pos.IsLocked,emp.DutyLevel,emp.OKDate,emp.Gender,emp.Birthday,pos.YEAR,
emp.Political,emp.Native,ee.ZhuanKeSchool,ee.BenKeSchool,ee.ShuoShiSchool,ee.BoShiSchool,
--ee.School ZhuanKeSchool,ee.Professional ZhuanKeProfessional
--,ee1.School BenKeSchool,ee1.Professional BenKeProfessional
--,ee2.School ShuoShiSchool,ee2.Professional ShuoShiProfessional
--,ee3.School BoShiSchool,ee3.Professional BoShiProfessional,
ee4.EducationName TopEducationName,
emp.FullEducationBg,emp.FullDegree,emp.Phone,emp.Health,emp.Marriage,emp.HomeAddress,emp.email
  from  PublicOfficialSelection pos 
 left join
 (select * from PublicOfficialRecord where GeRenStatus = 1) por on pos.ID = por.PublicOfficialSelectionID
left join Emp_BasicInfoRegister emp  on emp.EmpID = por.EmpID
left join OrgForHR o on o.ID = emp.OrgID
left join OrgForHR oo on oo.ID = pos.OrgID
left join 
(select empbasicid, max(case EducationName when '大专' then (School +'-'+ Professional) else '' end) ZhuanKeSchool,
max(case EducationName when '大学' then (School +'-'+ Professional) else '' end) BenKeSchool,
max(case EducationName when '硕士研究生' then (School +'-'+ Professional) else '' end) ShuoShiSchool,
max(case EducationName when '博士研究生' then (School +'-'+ Professional) else '' end) BoShiSchool
 from Emp_Education group by EmpBasicID) ee on ee.EmpBasicID = emp.EmpID
left join 
(select EducationName,EmpBasicID from Emp_Education where IsTopEducation = 1) ee4 on ee4.EmpBasicID = emp.EmpID
left join
(select COUNT(ID) Cou,EmpID from PublicOfficialRecord group by EmpID) aa on emp.EmpID = aa.EmpID) aa
where aa.Status1 = 4 ";
            if(year != 0)
                sql += " and aa.Year = " + year;
            sql += " and aa.IsLocked = '" + islocked + "'";
            sql += " order by aa.Department,aa.Department_Duty,aa.Duty";
 
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity4(dr));
            }
            return rights;
        }

        /// <summary>
        /// 包含所有信息
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialRecordModel> GetPublicOfficialRecordAll(int start, int end)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = @"select top " + (end - start) + @" ROW_NUMBER() OVER(ORDER BY por.ID ) Sec,
o.Name Unit,pos.Duty Duty,pos.Department,por.IsTongYi,pos.IsLocked,emp.Name EmpName,emp.CardNo,oo.Name NowUnit,por.CheckMemo,emp.PositionName NowDuty,0 as ID,0 as EmpID,0 as MingCi,pos.FaBuType,0 as BiShiScore,0 as MianShiScore,0 as TotalScore
 from PublicOfficialRecord por 
inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
inner join Emp_BasicInfo emp on emp.ID = por.EmpID
inner join OrgForHR o on o.ID = pos.OrgID
inner join OrgForHR oo on oo.ID = emp.OrgID
 where por.ID not   in   
(select   top " + start + @" ID from PublicOfficialSelection ) ";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity(dr));
            }
            return rights;
        }

        /// <summary>
        /// 包含所有信息
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialRecordModel> GetPublicOfficialRecordAllForDaoChu(string PublicOfficialSelectionID)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            //            string sql = @"select top " + (end - start) + @" 
            //o.Name Unit,pos.Duty Duty,pos.Department,emp.Name EmpName,emp.CardNo,oo.Name NowUnit,por.CheckMemo,emp.PositionName NowDuty,0 as ID,0 as EmpID
            // from PublicOfficialRecord por 
            //inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
            //inner join Emp_BasicInfo emp on emp.ID = por.EmpID
            //inner join OrgForHR o on o.ID = pos.OrgID
            //inner join OrgForHR oo on oo.ID = emp.OrgID
            // where por.ID not   in   
            //(select   top " + start + @" ID from PublicOfficialSelection ) ";
            string sql = @"select  o.Name Unit,pos.Duty Duty,pos.Department,emp.Name EmpName,emp.CardNo,oo.Name NowUnit,0 as MingCi,por.IsTongYi,pos.FaBuType,
por.CheckMemo,emp.PositionName NowDuty,0 as ID,0 as EmpID,'' as Status,'' as GeRenStatus,pos.IsLocked,
0 as PublicOfficialSelectionID, 0 as PublicOfficialRecordID,GETDATE() Date,0 as BaoMingCount,0 as BiShiScore,0 as MianShiScore,0 as TotalScore,
0 as BaoMingCount1,0 as DaiShenHeCount
 from PublicOfficialRecord por 
inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
inner join Emp_BasicInfo emp on emp.ID = por.EmpID
inner join OrgForHR o on o.ID = pos.OrgID
inner join OrgForHR oo on oo.ID = emp.OrgID
where pos.ID in (" + PublicOfficialSelectionID + ")";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity(dr));
            }
            return rights;
        }

        /// <summary>
        /// 通过审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CheckPublicOfficialRecordModel(string ID, string type)
        {
            string checksql = @"select COUNT(por.ID) Cou from PublicOfficialRecord por inner join PublicOfficialSelection pos 
on por.PublicOfficialSelectionID = pos.ID   
where GeRenStatus = 1 and pos.IsLocked = 0 and EmpID = 
(select EmpID from PublicOfficialRecord where ID in " + ID + ") ";
            int count = (int)AccessHelper.GetInstance().ExcuteDataSet(checksql).Tables[0].Rows[0][0];
            if (count >= 1) return false;
            else
            {
                //                string checksql1 = @"select COUNT(ID) Cou from PublicOfficialRecord
                //where GeRenStatus = 1 and EmpID = 
                //(select EmpID from PublicOfficialRecord where ID in " + ID + ")";
                //                int count1 = (int)AccessHelper.GetInstance().ExcuteDataSet(checksql1).Tables[0].Rows[0][0];
                string sql = "";
                if (type == "竞争性选拔")
                    sql = string.Format(@"update dbo.PublicOfficialRecord set GeRenStatus = 1 where ID in {0};
update PublicOfficialRecord set GeRenStatus = 4 where EmpID in (
select distinct EmpID from PublicOfficialRecord where ID in {0}) and GeRenStatus = 0;
insert into dbo.PublicOfficialRecord_BiShi
select ID from PublicOfficialRecord where ID in {0}", ID);
                else
                    sql = string.Format(@"update dbo.PublicOfficialRecord set GeRenStatus = 1 where ID in {0};
update PublicOfficialRecord set GeRenStatus = 4 where EmpID in (
select distinct EmpID from PublicOfficialRecord where ID in {0}) and GeRenStatus = 0;
insert into dbo.PublicOfficialRecord_MianShi
select ID from PublicOfficialRecord where ID in {0}", ID);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return true;
            }

        }

        /// <summary>
        /// 取消审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CancelCheckPublicOfficialRecordModel(string ID, string type)
        {
            string sql = "";
            if (type == "竞争性选拔")
                sql = string.Format(@"update dbo.PublicOfficialRecord set GeRenStatus = 0 where ID in {0};
update PublicOfficialRecord set GeRenStatus = 0 where EmpID in (
select distinct EmpID from PublicOfficialRecord where ID in {0}) and GeRenStatus = 4;
delete from PublicOfficialRecord_BiShi where PublicOfficialRecordID in {0}", ID);
            else
                sql = string.Format(@"update dbo.PublicOfficialRecord set GeRenStatus = 0 where ID in {0};
update PublicOfficialRecord set GeRenStatus = 0 where EmpID in (
select distinct EmpID from PublicOfficialRecord where ID in {0}) and GeRenStatus = 4;
delete from PublicOfficialRecord_MianShi where PublicOfficialRecordID in {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;


        }

        public bool Check(string CardNo, string Name, int status, int positionID)
        {
            if (status == 2)
                if (!CanPass(CardNo, Name))
                    return false;
            string sql = string.Format("update RegisterPosition set [Status]={3} where Name='{0}'  and CardNo='{1}' and PositionID= {2}", Name, CardNo, positionID, status);
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql);
            if (count == 1)
                return true;
            return false;
        }

        /// <summary>
        /// 是否可以通过审核
        /// </summary>
        /// <param name="cardNo"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool CanPass(string cardNo, string name)
        {
            int year = DateTime.Now.Year;
            string sql = string.Format(@"select COUNT(*) from RegisterPosition r
inner join PublicOfficialSelection s on s.ID = r.PositionID and s.Status = 4 and s.[Year]={2}
where r.Status = 2 and r.CardNo='{0}' and r.Name='{1}'", cardNo, name, year);
            int count = (int)AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows[0][0];
            if (count > 0)
                return false;
            else
                return true;
        }

        /// <summary>
        /// 退回
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool TuiPublicOfficialRecordModel(string ID, string memo)
        {
            string sql = "update dbo.PublicOfficialRecord set GeRenStatus = 2,CheckMemo = '" + memo + "' where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 锁定本期工作
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool SuoPublicOfficialSelection()
        {
            string sql = "update dbo.PublicOfficialSelection set islocked = 1 where islocked = 0 ";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 查看每个人的报名的职位,ID为0的话，就是在分数管理的时候显示通过审核的人的信息
        /// </summary>
        /// <param name="ID"></param>
        /// /// <param name="isc">为false，则用于设置成绩时打开的窗体</param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialRecordModel> GetPublicOfficialRecordbyEmpID(int ID, bool isc, int nian, string islocked, int exameid)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = "";

            sql =
                @" select ROW_NUMBER() OVER(ORDER BY por.ID ) Sec, emp.Name EmpName,emp.CardNo,o.Name Unit,pos.Department_Duty Department,0 as ID,0 as MingCi,pos.FaBuType,por.IsTongYi,
 pos.Duty,por.ID PublicOfficialRecordID,oo.Name NowUnit,emp.PositionName NowDuty,pos.IsLocked,
 por.CheckMemo ,por.GeRenStatus,'' Status,GETDATE() Date,pos.id as PublicOfficialSelectionID,
 0 as BaoMingCount,0 as BaoMingCount1,0 as DaiShenHeCount,'' as CheckMemo,emp.ID EmpID,por.BiShiScore as BiShiScore,por.MianShiScore as MianShiScore,por.TotalScore
  from PublicOfficialRecord por 
 inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
 inner join Emp_BasicInfo emp on por.EmpID = emp.ID
 inner join OrgForHR o on o.ID = pos.OrgID
 inner join OrgForHR oo on oo.ID = emp.OrgID
 where por.GeRenStatus != 3 and 1=1 ";
            if (isc)
            {
                if (nian != 0) sql += " and pos.Year = " + nian;
                if (ID != 0)
                {
                    sql += " and EmpID =" + ID;
                }
                else
                {
                    sql += " and por.GeRenStatus = 1";
                }
            }
            else
            {

                sql += " and por.ID = " + ID;
            }
            if (exameid != 0)
                sql += "  and pos.ExameID = " + exameid;
            sql += " and pos.islocked = '" + islocked + "'";
            sql += "  order by o.ID,pos.Department_Duty,pos.Duty,por.BiShiScore desc,por.MianShiScore desc";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity(dr));
            }
            return rights;
        }

        /// <summary>
        /// 查看笔试人员
        /// </summary>
        /// <param name="type">笔试还是面试</param>
        /// <returns></returns>
        public ObservableCollection<PublicOfficialRecordModel> GetPublicOfficialRecordbyEmpID(string type, int nian, string fabutype, string islocked)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = "";

            sql =
                @" select  emp.Name EmpName,emp.CardNo,o.Name Unit,pos.Department_Duty Department,pb.ID as ID, ROW_NUMBER() OVER(ORDER BY por.TotalScore desc) Sec, 
ROW_NUMBER() OVER(ORDER BY por.TotalScore desc) as MingCi,
 pos.Duty,por.ID PublicOfficialRecordID,oo.Name NowUnit,emp.PositionName NowDuty,
 por.CheckMemo ,por.GeRenStatus,'' Status,GETDATE() Date,0 as PublicOfficialSelectionID,pos.FaBuType,por.IsTongYi,pos.IsLocked,
 0 as BaoMingCount,0 as BaoMingCount1,0 as DaiShenHeCount,'' as CheckMemo,emp.ID EmpID,por.BiShiScore as BiShiScore,por.MianShiScore as MianShiScore,por.TotalScore
  from PublicOfficialRecord por 
 inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
 inner join Emp_BasicInfo emp on por.EmpID = emp.ID
 inner join OrgForHR o on o.ID = pos.OrgID
 inner join OrgForHR oo on oo.ID = emp.OrgID";
            if (type == "笔试")
            {
                sql += @" inner join PublicOfficialRecord_BiShi pb on pb.PublicOfficialRecordID = por.ID
 where 1=1 ";
                if (nian != 0) sql += " and pos.Year = " + nian;
                sql += "  order by o.ID,pos.Duty,por.BiShiScore desc";
            }
            if (type == "面试")
            {
                sql += @" inner join PublicOfficialRecord_MianShi pb on pb.PublicOfficialRecordID = por.ID
 where 1=1 ";
                if (nian != 0) sql += " and pos.Year = " + nian;
                if (fabutype == "竞争性选拔")
                {
                    sql += "  and pos.FaBuType = '竞争性选拔' ";
                    sql += " and pos.IsLocked = '" + islocked + "'";
                    sql += " order by o.ID,pos.Duty,por.TotalScore desc";
                }
                if (fabutype == "选调交流")
                {
                    sql += "  and pos.FaBuType = '选调交流' ";
                    sql += " and pos.IsLocked = '" + islocked + "'";
                    sql += " order by o.ID,pos.Department_Duty,pos.Duty,por.MianShiScore desc";
                }
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<PublicOfficialRecordModel> GetPublicOfficialRecordbyEmpIDForDaoChu(string ids, string kaoshileixing)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = "";

            sql =
                @"select emp.Name EmpName,emp.CardNo,o.Name Unit,pos.Department Department,pb.ID as ID,pos.IsLocked,
 pos.Duty,por.ID PublicOfficialRecordID,oo.Name NowUnit,emp.PositionName NowDuty,
 por.CheckMemo ,por.GeRenStatus,'' Status,GETDATE() Date,0 as PublicOfficialSelectionID,pos.FaBuType,por.IsTongYi,
 0 as BaoMingCount,0 as BaoMingCount1,0 as DaiShenHeCount,'' as CheckMemo,emp.ID EmpID,por.BiShiScore as BiShiScore,
 por.MianShiScore as MianShiScore,por.TotalScore,zz.Zu,
 SUBSTRING(convert(varchar(10),zz.RiQi,120),6,2) + '月' + 
 SUBSTRING(convert(varchar(10),zz.RiQi,120),9,2) + '日' +
 case when Convert(int,SUBSTRING(zz.StartShi,0,3))<13 then '上午' else '下午' end 
 as RiQi,ROW_NUMBER() OVER(ORDER BY por.TotalScore desc) as MingCi,
 Convert(decimal(18,2),por.BiShiScore*0.4) BiShiFenZheSuan,
 Convert(decimal(18,2),por.MianShiScore*0.4) MianShiFenZheSuan,
 ROW_NUMBER() OVER(ORDER BY por.TotalScore desc) Sec
  from PublicOfficialRecord por 
 inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
 inner join Emp_BasicInfo emp on por.EmpID = emp.ID
 inner join OrgForHR o on o.ID = pos.OrgID
 inner join OrgForHR oo on oo.ID = emp.OrgID";
            if (kaoshileixing == "笔试")
                sql += " inner join PublicOfficialRecord_BiShi pb on pb.PublicOfficialRecordID = por.ID";
            else
                sql += " inner join PublicOfficialRecord_MianShi pb on pb.PublicOfficialRecordID = por.ID";
            sql += @" left join ZhiWeiShiJianAnPai zz on zz.PublicOfficialSelectionID = pos.ID
 where pb.ID in " + ids;
            sql += "    order by o.Name,pos.Department ";

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity1(dr));
            }
            return rights;
        }

        /// <summary>
        /// 设置分数
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool UpdatePublicOfficialRecordModelFenShu(PublicOfficialRecordModel model)
        {
            string sql = "update dbo.PublicOfficialRecord set BiShiScore = " + model.BiShiScore + ", MianShiScore = " +
                         model.MianShiScore + ", TotalScore = " +
                         model.TotalScore + " where ID = " + model.PublicOfficialRecordID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 生成笔试通知
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool BiShi(int ID)
        {
            string sql1 =
                string.Format(
                    @"select Count(ID) Cou from PublicOfficialRecord_BiShi where PublicOfficialRecordID = {0}",
                    ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql1);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0)
                {
                    return false;
                }
                else
                {
                    string sql = string.Format(@"INSERT INTO PublicOfficialRecord_BiShi
           ([PublicOfficialRecordID])
     VALUES
           ({0})", ID);
                    if (!string.IsNullOrEmpty(sql))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    return true;
                }
            }
            else
            {
                string sql = string.Format(@"INSERT INTO PublicOfficialRecord_BiShi
           ([PublicOfficialRecordID])
     VALUES
           ({0})", ID);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return true;
            }

        }

        /// <summary>
        /// 撤销笔试通知
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CheXiaoBiShi(List<int> PublicOfficialRecordID)
        {
            foreach (int id in PublicOfficialRecordID)
            {
                string sql1 =
                string.Format(
                    @"select BiShiScore from PublicOfficialRecord where ID = {0}",
                    id);
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql1);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    if (
                        Convert.ToDecimal(string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString())
                                              ? 0
                                              : ds.Tables[0].Rows[0][0]) > 0)
                    {
                        return false;
                    }
                }
            }
            foreach (int id in PublicOfficialRecordID)
            {
                string sql = string.Format(@"delete from PublicOfficialRecord_BiShi where PublicOfficialRecordID = {0}", id);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            return true;
        }

        /// <summary>
        /// 生成面试通知
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool MianShi(List<int> ID)
        {
            foreach (int id in ID)
            {
                string sql1 =
                string.Format(
                    @"select Count(ID) Cou from PublicOfficialRecord_MianShi where PublicOfficialRecordID = {0}",
                    id);
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql1);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0)
                    {
                        return false;
                    }
                }
            }
            foreach (int id in ID)
            {
                string sql = string.Format(@"INSERT INTO PublicOfficialRecord_MianShi
           ([PublicOfficialRecordID])
     VALUES
           ({0})", id);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);

            }
            return true;
        }

        //public FileStream ShengChengMianShiTongZhi(string EmpName)
        //{
        //    XWPFDocument doc = new XWPFDocument();
        //    XWPFParagraph p1 = doc.CreateParagraph();
        //    XWPFRun r1 = p1.CreateRun();
        //    r1.SetText("                    考 生 通 知");
        //    r1.SetFontSize(20);
        //    r1.SetBold(true);
        //    r1.SetFontFamily("仿宋_GB2312");

        //    XWPFRun r2 = p1.CreateRun();
        //    r2.SetText(EmpName + " 考生：");
        //    r2.SetFontFamily("仿宋_GB2312");

        //    XWPFRun r3 = p1.CreateRun();
        //    r3.SetText(EmpName + " 考生：");
        //    r3.SetFontFamily("仿宋_GB2312");
        //    return new FileStream(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Guid.NewGuid().ToString() + ".doc"), FileMode.Create);
        //}

        /// <summary>
        /// 撤销面试通知
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CheXiaoMianShi(List<int> PublicOfficialRecordID)
        {
            foreach (int id in PublicOfficialRecordID)
            {
                string sql1 =
                string.Format(
                    @"select MianShiScore from PublicOfficialRecord where ID = {0}",
                    id);
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql1);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    if (
                        Convert.ToDecimal(string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString())
                                              ? 0
                                              : ds.Tables[0].Rows[0][0]) > 0)
                    {
                        return false;
                    }
                }
            }
            foreach (int id in PublicOfficialRecordID)
            {
                string sql = string.Format(@"delete from PublicOfficialRecord_MianShi where PublicOfficialRecordID = {0}", id);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            return true;
        }

        /// <summary>
        /// 拟同意或放弃
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool NiTongYiorNot(List<int> PublicOfficialRecordID, string istongyi)
        {
            foreach (int id in PublicOfficialRecordID)
            {
                string sql = string.Format(@"update dbo.PublicOfficialRecord set IsTongYi = '{1}' where ID = {0}", id, istongyi);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            return true;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="year"></param>
        /// <param name="tiaojian"></param>
        /// <param name="type">交流选拔或招录管理</param>
        /// <returns></returns>
        public ObservableCollection<ZhiWeiShiJianAnPaiModel> PublicOfficialSelectionForAnPai(int year, string tiaojian)
        {
            ObservableCollection<ZhiWeiShiJianAnPaiModel> rights = new ObservableCollection<ZhiWeiShiJianAnPaiModel>();
            string sql = @"select distinct pos.ID, ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.* from 
(select distinct p.ID,p.Status,p.Year,p.FaBuType,p.IsLocked,o.Name OrgName from PublicOfficialSelection p 
inner join OrgForHR o on p.OrgID=o.ID
inner join PublicOfficialRecord por on por.PublicOfficialSelectionID = p.ID
 where p.Status = 4 and p.year = " + year + @"
and FaBuType = '" + tiaojian + @"' and p.IsLocked = 0 
and por.GeRenStatus = 1) pos 
left join ZhiWeiShiJianAnPai zw on pos.ID = zw.PublicOfficialSelectionID
where  pos.Status = 4  and pos.Year = " + year + " and pos.FaBuType = '" + tiaojian + "' and pos.IsLocked = 0 ";
            sql += " order by RiQi,Sec1 ,EndShi ";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhiWeiShiJianAnPaiModel.FillEntity(dr));
            }
            return rights;
        }

        public Dictionary<int, int> GetMianShiRenShu(int PublicOfficialSelectionID,int year)
        {
            Dictionary<int, int> dic = new Dictionary<int, int>();
            string sql =
                @"select COUNT(m.ID) Cou ,por.PublicOfficialSelectionID from PublicOfficialRecord_MianShi m 
inner join PublicOfficialRecord por on m.PublicOfficialRecordID = por.ID
inner join PublicOfficialSelection pos on pos.ID = por.PublicOfficialSelectionID where pos.IsLocked = '0' and pos.Year = " + year + " and 1=1 ";
            if (PublicOfficialSelectionID != 0)
                sql += " and por.PublicOfficialSelectionID =" + PublicOfficialSelectionID;
            sql += " group by por.PublicOfficialSelectionID";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                dic.Add(Convert.ToInt32(dr[1]), Convert.ToInt32(dr[0]));
            }
            return dic;
        }

        public bool InsertZu(ZhiWeiShiJianAnPaiModel model,string type)
        {
            switch(model.Zu)
            {
                case "第一组":
                    model.Sec1 = 1;
                    break;
                case "第二组":
                    model.Sec1 = 2;
                    break;
                case "第三组":
                    model.Sec1 = 3;
                    break;
                case "第四组":
                    model.Sec1 = 4;
                    break;
                case "第五组":
                    model.Sec1 = 5;
                    break;
                case "第六组":
                    model.Sec1 = 6;
                    break;
                case "第七组":
                    model.Sec1 = 7;
                    break;
                case "第八组":
                    model.Sec1 = 8;
                    break;
                case "第九组":
                    model.Sec1 = 9;
                    break;
            }
            string sql = string.Format(@"delete from ZhiWeiShiJianAnPai where PublicOfficialSelectionID = {0} and type = '{17}'; INSERT INTO ZhiWeiShiJianAnPai
           ([PublicOfficialSelectionID]
           ,[Year]
           ,[Zu]
           ,[RiQi]
           ,[ZhuKaoGuan]
           ,[ZhuanYeKaoGuan1]
           ,[ZhuanYeKaoGuan2]
           ,[DanWeiKaoGuan1]
           ,[DanWeiKaoGuan2]
           ,[MianShiJianDuYuan]
           ,[GongZuoRenGuan1]
           ,[GongZuoRenGuan2]
           ,[StartShi]
           ,[RenShu]
           ,[DiDian]
           ,[EndShi],Sec1,type)
     VALUES
           ({0}
           ,{1}
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}','{16}','{17}')", model.PublicOfficialSelectionID, model.Year, 
                    model.Zu,
                    model.RiQi, model.ZhuKaoGuan, model.ZhuanYeKaoGuan1, model.ZhuanYeKaoGuan2, model.DanWeiKaoGuan1
                    , model.DanWeiKaoGuan2, model.MianShiJianDuYuan, model.GongZuoRenGuan1, model.GongZuoRenGuan2, model.StartShi,
                    model.RenShu, model.DiDian, model.EndShi,model.Sec1, type);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public ObservableCollection<ZhiWeiShiJianAnPaiModel> PublicOfficialSelectionForAnPaiSuoDing(int year, string tiaojian)
        {
            ObservableCollection<ZhiWeiShiJianAnPaiModel> rights = new ObservableCollection<ZhiWeiShiJianAnPaiModel>();
            string sql = "";
//            sql = @"select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.RiQi ,zw.Zu,pos.Duty,'' ZhuKaoGuanS,'' ZhuanYeKaoGuan1S,
//'' ZhuanYeKaoGuan2S,'' DanWeiKaoGuan1S,zw.ID,
//'' DanWeiKaoGuan2S,'' MianShiJianDuYuan,'' GongZuoRenGuan1,'' GongZuoRenGuan2,'' Shi,'' RenShu,'' DiDian from PublicOfficialSelection pos  left join ZhiWeiShiJianAnPai zw on pos.ID = zw.PublicOfficialSelectionID
//where  pos.IsLocked = 1 and Status = 4 and pos.Year = " + year + " and pos.FaBuType = '" + tiaojian + "'";
            sql = @"select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.RiQi ,zw.Zu,pos.Duty,k.Name ZhuKaoGuanS,kk.Name ZhuanYeKaoGuan1S,
kkk.Name ZhuanYeKaoGuan2S,kkkk.Name DanWeiKaoGuan1S,zw.ID,
kkkkk.Name DanWeiKaoGuan2S,zw.MianShiJianDuYuan,zw.GongZuoRenGuan1,zw.GongZuoRenGuan2,zw.StartShi,zw.EndShi,
zw.RenShu,zw.DiDian 
from PublicOfficialSelection pos  
left join ZhiWeiShiJianAnPai zw on pos.ID = zw.PublicOfficialSelectionID
left join KaoGuan k on k.ID = zw.ZhuKaoGuan
left join KaoGuan kk on kk.ID = zw.ZhuanYeKaoGuan1
left join KaoGuan kkk on kkk.ID = zw.ZhuanYeKaoGuan2
left join KaoGuan kkkk on kkkk.ID = zw.DanWeiKaoGuan1
left join KaoGuan kkkkk on kkkkk.ID = zw.DanWeiKaoGuan2
where  Status = 4 and pos.Year = " + year + " and pos.FaBuType = '" + tiaojian + "' and pos.IsLocked = 1 ";
            //if (tiaojian == "竞争性选拔")
            //{
            //    sql += " and IsJZXXB  = 1";
            //}
            //else
            //{
            //    sql += " and IsXDJL  = 1";
            //}
            sql += " order by Sec";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhiWeiShiJianAnPaiModel.FillEntity1(dr));
            }
            return rights;
        }

        public ObservableCollection<ZhiWeiShiJianAnPaiModel> GetOneAnPaiSuoDing(int id)
        {
            ObservableCollection<ZhiWeiShiJianAnPaiModel> rights = new ObservableCollection<ZhiWeiShiJianAnPaiModel>();
            string sql = "";
            sql = @"select ROW_NUMBER() OVER(ORDER BY zhi.ID ) Sec, zhi.RiQi,zhi.Zu,pos.Duty,k.Name ZhuKaoGuanS,kk.Name ZhuanYeKaoGuan1S,
kkk.Name ZhuanYeKaoGuan2S,kkkk.Name DanWeiKaoGuan1S,zhi.ID,
kkkkk.Name DanWeiKaoGuan2S,zhi.MianShiJianDuYuan,zhi.GongZuoRenGuan1 ,
zhi.GongZuoRenGuan2,zhi.StartShi + '-' + zhi.EndShi Shi,zhi.RenShu,zhi.DiDian from ZhiWeiShiJianAnPai zhi 
inner join PublicOfficialSelection pos on zhi.PublicOfficialSelectionID = pos.ID
left join KaoGuan k on k.ID = zhi.ZhuKaoGuan
left join KaoGuan kk on kk.ID = zhi.ZhuanYeKaoGuan1
left join KaoGuan kkk on kkk.ID = zhi.ZhuanYeKaoGuan2
left join KaoGuan kkkk on kkkk.ID = zhi.DanWeiKaoGuan1
left join KaoGuan kkkkk on kkkkk.ID = zhi.DanWeiKaoGuan2";
            sql += " where zhi.ID =" + id;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhiWeiShiJianAnPaiModel.FillEntity1(dr));
            }
            return rights;
        }


        public ObservableCollection<PublicOfficialRecordModel> GetMianShiTongZhiBiao(int year, string FaBuType,string mianshi)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = "";
            sql =
                @"select ROW_NUMBER() OVER(ORDER BY a.RiQi,CONVERT(datetime, a.StartShi) , a.Department,a.Department_Duty,a.Duty) Sec,* from 
(select distinct por.id, oo.Name as Department,pos.Duty,pos.Department_Duty,emp.Name EmpName,por.BiShiScore,emp.CardNo,
o.Name OrgName,emp.PositionName,emp.DutyLevel,emp.OKDate,emp.Gender,emp.Birthday,zz.didian MianShiDiZhi,
emp.Political,emp.Native,ee.ZhuanKeSchool,ee.BenKeSchool,ee.ShuoShiSchool,ee.BoShiSchool,
--ee.School ZhuanKeSchool,ee.Professional ZhuanKeProfessional
--,ee1.School BenKeSchool,ee1.Professional BenKeProfessional
--,ee2.School ShuoShiSchool,ee2.Professional ShuoShiProfessional
--,ee3.School BoShiSchool,ee3.Professional BoShiProfessional,
ee4.EducationName TopEducationName,
emp.FullEducationBg,emp.FullDegree,emp.Phone,emp.Health,emp.Marriage,emp.HomeAddress,emp.email,
zz.Zu,zz.RiQi,(case when datepart(hh,zz.StartShi)>9 then cast(datepart(hh,zz.StartShi) as varchar(2)) else '0' + cast(datepart(hh,zz.StartShi) as varchar(2)) end + 
':' + (case when datepart(mi,zz.StartShi)>9 then cast(datepart(mi,zz.StartShi) as varchar(2)) else '0' + cast(datepart(mi,zz.StartShi) as varchar(2)) end+ 
':' + (case when datepart(ss,zz.StartShi)>9 then cast(datepart(ss,zz.StartShi) as varchar(2)) else '0' + cast(datepart(ss,zz.StartShi) as varchar(2)) end))
 + '-' + case when datepart(hh,zz.endshi)>9 then cast(datepart(hh,zz.endshi) as varchar(2)) else '0' + cast(datepart(hh,zz.endshi) as varchar(2)) end + 
':' + (case when datepart(mi,zz.endshi)>9 then cast(datepart(mi,zz.endshi) as varchar(2)) else '0' + cast(datepart(mi,zz.endshi) as varchar(2)) end+ 
':' + (case when datepart(ss,zz.endshi)>9 then cast(datepart(ss,zz.endshi) as varchar(2)) else '0' + cast(datepart(ss,zz.endshi) as varchar(2)) end))) as Shi,zz.StartShi
from Emp_BasicInfoRegister emp 
inner join PublicOfficialRecord por on emp.EmpID = por.EmpID
inner join PublicOfficialSelection pos on pos.ID = por.PublicOfficialSelectionID
inner join PublicOfficialRecord_MianShi pom on pom.PublicOfficialRecordID = por.ID
inner join OrgForHR o on o.ID = emp.OrgID
inner join OrgForHR oo on oo.ID = pos.OrgID
inner join Emp_Duty ed on ed.EmpbasicID = emp.EmpID
left join ZhiWeiShiJianAnPai zz on zz.PublicOfficialSelectionID = pos.ID
left join 
(select empbasicid, max(case EducationName when '大专' then (School +'-'+ Professional) else '' end) ZhuanKeSchool,
max(case EducationName when '大学' then (School +'-'+ Professional) else '' end) BenKeSchool,
max(case EducationName when '硕士研究生' then (School +'-'+ Professional) else '' end) ShuoShiSchool,
max(case EducationName when '博士研究生' then (School +'-'+ Professional) else '' end) BoShiSchool
 from Emp_Education group by EmpBasicID) ee on ee.EmpBasicID = emp.EmpID
left join 
(select EducationName,EmpBasicID from Emp_Education where IsTopEducation = 1) ee4 on ee4.EmpBasicID = emp.EmpID
where por.GeRenStatus = 1 and pos.Status = 4 and pos.IsLocked = 0 and pos.Year = " +
                year;
                                                                                   //+ " and zz.Year = " + year;
            if (!string.IsNullOrEmpty(FaBuType))
                sql += " and FaBuType = '" + FaBuType + "'";
            if(mianshi != "面试")
            sql += " ) a order by a.ID";
            else
            {
                sql += " ) a order by a.RiQi,CONVERT(datetime, a.StartShi) , a.Department,a.Department_Duty,a.Duty";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity4(dr));
            }
            return rights;
        }

        #endregion

        #region 通知
        public ObservableCollection<TongZhiModel> GetTongZhiModel(int year, int id)
        {
            ObservableCollection<TongZhiModel> rights = new ObservableCollection<TongZhiModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY ID ) Sec,* from tongzhi where 1=1 ");
            if (year != 0)
            {
                sql += " and year = " + year;
            }
            if (id != 0)
            {
                sql += " and ID = " + id;
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(TongZhiModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool AddTongZhiModel(TongZhiModel item)
        {
            string sql = string.Format(@"INSERT INTO TongZhi
           ([Year]
           ,[Type]
           ,[Content])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}')", item.Year, item.Type, item.Content);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateTongZhiModel(TongZhiModel item)
        {
            string sql = string.Format(@"UPDATE TongZhi
   SET [Year] = {0}
      ,[Type] = '{1}'
      ,[Content] = '{2}'
 WHERE ID = {3}", item.Year, item.Type, item.Content, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeteleTongZhiModel(int ID)
        {
            string sql = "delete from TongZhi where ID = " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 考官管理
        public ObservableCollection<KaoGuanModel> GetKaoGuanModel(string name, int id, string isadmin,string kaoguantype)
        {
            ObservableCollection<KaoGuanModel> rights = new ObservableCollection<KaoGuanModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY ID ) Sec,*,0 as KaoGuanID,0 as Year,'' as XBType,'' as KGType from KaoGuan where type = '{0}' and 1=1 ",kaoguantype);
            //if (year != 0)
            //{
            //    sql += " and year = " + year;
            //}
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and Name = '" + name + "'";

            }
            if (id != 0)
            {
                sql += " and ID = " + id;
            }
            else
            {
                if (isadmin != "admin") sql += " and creator = '" + isadmin + "'";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(KaoGuanModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool AddKaoGuanModel(KaoGuanModel item)
        {
            string sql = string.Format(@"INSERT INTO KaoGuan
           ([YongRenDanWei]
           ,[Name]
           ,[CardNo]
           ,[GongZuoDanWei]
           ,[ZhiWuZhiCheng]
           ,[LianXiFangShi]
           ,[IsKaoGuanZheng]
           ,[Memo]
           ,[EMail]
            ,[Creator]
           ,[CreateTime],RiQi,type)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}','{9}','{10}','{11}','{12}')", item.YongRenDanWei, item.Name, item.CardNo, item.GongZuoDanWei, item.ZhiWuZhiCheng, item.LianXiFangShi, item.IsKaoGuanZheng
                    , item.Memo, item.EMail, item.Creator, DateTime.Today,item.RiQi,item.Type);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateKaoGuanModel(KaoGuanModel item)
        {
            string sql = string.Format(@"UPDATE KaoGuan
   SET [YongRenDanWei] = '{0}'
      ,[Name] = '{1}'
      ,[CardNo] = '{2}'
      ,[GongZuoDanWei] = '{3}'
      ,[ZhiWuZhiCheng] = '{4}'
      ,[LianXiFangShi] = '{5}'
      ,[IsKaoGuanZheng] = '{6}'
      ,[Memo] = '{7}'
      ,[EMail] = '{8}',RiQi = '{9}',type='{10}'
 WHERE ID = {11}", item.YongRenDanWei, item.Name, item.CardNo, item.GongZuoDanWei, item.ZhiWuZhiCheng, item.LianXiFangShi, item.IsKaoGuanZheng
                    , item.Memo, item.EMail, item.RiQi,item.Type, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeteleKaoGuanModel(string ID)
        {
            string sql = "delete from KaoGuan where ID in " + ID ;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 考官明细管理
        public ObservableCollection<KaoGuanModel> GetKaoGuanDetailModel(string name, int year, int id,string kaoguantype)
        {
            ObservableCollection<KaoGuanModel> rights = new ObservableCollection<KaoGuanModel>();
            string sql = string.Format(@"
SELECT ROW_NUMBER() OVER(ORDER BY kgd.xbtype,kgd.kgtype ) Sec,[YongRenDanWei]
      ,[Name]
      ,[CardNo]
      ,[GongZuoDanWei]
      ,[ZhiWuZhiCheng]
      ,[LianXiFangShi]
      ,[IsKaoGuanZheng]
      ,[Memo]
      ,[EMail],kgd.ID,kgd.KaoGuanID,kgd.year,kgd.xbtype,kgd.kgtype,k.Creator,k.CreateTime,k.RiQi
  FROM kaoguandetail kgd inner join KaoGuan k 
on kgd.kaoguanid = k.ID  where type = '{0}' and 1=1 ", kaoguantype);
            if (year != 0)
            {
                sql += " and kgd.year = " + year;
            }
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and Name = '" + name + "'";
            }
            if (id != 0)
            {
                sql += " and kgd.ID = " + id;
            }
            sql += " order by kgd.xbtype,kgd.kgtype";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(KaoGuanModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool AddKaoGuanDetailModel(KaoGuanDetailModel item)
        {
            string sql1 =
                string.Format(@"select Count(ID) Cou from KaoGuanDetail where KaoGuanID = {0} and Year = {1} and XBType = '{2}'",
                              item.KaoGuanID, item.Year, item.XBType);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql1);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0)
                {
                    return false;
                }
                else
                {
                    string sql = string.Format(@"INSERT INTO KaoGuanDetail
           ([KaoGuanID]
           ,[Year]
           ,[XBType]
           ,[KGType])
     VALUES
           ({0}
           ,{1}
           ,'{2}'
           ,'{3}')", item.KaoGuanID, item.Year, item.XBType, item.KGType);
                    if (!string.IsNullOrEmpty(sql))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    return true;

                }
            }
            else
            {
                string sql = string.Format(@"INSERT INTO KaoGuanDetail
           ([KaoGuanID]
           ,[Year]
           ,[XBType]
           ,[KGType])
     VALUES
           ({0}
           ,{1}
           ,'{2}'
           ,'{3}')", item.KaoGuanID, item.Year, item.XBType, item.KGType);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return true;
            }
        }
        public bool UpdateKaoGuanDetailModel(KaoGuanDetailModel item)
        {
            string sql = string.Format(@"UPDATE KaoGuanDetail
   SET [KaoGuanID] = {0}
      ,[Year] = {1}
      ,[XBType] = '{2}'
      ,[KGType] = '{3}'
 WHERE ID = {4}", item.KaoGuanID, item.Year, item.XBType, item.KGType, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeteleKaoGuanDetailModel(int ID)
        {
            string sql = "delete from KaoGuanDetail where ID = " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        public ObservableCollection<PublicOfficialInfo> GetPublicOfficialInfo(string condition)
        {
            string sql = "select * from PublicOfficialInfo";
            if (!string.IsNullOrEmpty(condition))
                sql += " where " + condition;
            ObservableCollection<PublicOfficialInfo> collection = new ObservableCollection<PublicOfficialInfo>();
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    PublicOfficialInfo entity = new PublicOfficialInfo();
                    entity.ID = (int)dr["ID"];
                    entity.Name = dr["Name"] as string;
                    entity.Infomation = dr["Infomation"] as string;
                    entity.InputName = dr["InputName"] as string;
                    entity.InputTime = dr["InputTime"] as DateTime?;
                    entity.DSC = dr["DSC"] as string;
                    entity.Type = (int)dr["Type"];
                    entity.Status = (int)dr["Status"];
                    entity.Office = dr["Office"] as string;
                    entity.YM = dr["YM"] as DateTime?;
                    string subSQL = string.Format(@"select * from ExameOrg
  where ExameID ={0}", entity.ID);
                    DataSet ds1 = AccessHelper.GetInstance().ExcuteDataSet(subSQL);
                    if (ds1.Tables[0].Rows.Count > 0)
                    {
                        foreach (DataRow dr1 in ds1.Tables[0].Rows)
                        {
                            ExameOrg eo = new ExameOrg();
                            eo.ID = (int)dr1["ID"];
                            eo.ExameID = entity.ID;
                            eo.OrgForHRID = (int)dr1["OrgForHRID"];
                            entity.Orgs.Add(eo);
                        }
                    }
                    collection.Add(entity);
                }
            }

            return collection;
        }

        public bool SavePublicOfficialInfo(PublicOfficialInfo entity, Files[] fs, string XamltempFile)
        {
            try
            {
                string uploadFolder = System.Web.Hosting.HostingEnvironment.MapPath("~/cash");
                string filename = uploadFolder + @"\" + XamltempFile;
                string xaml = string.Empty;
                if (File.Exists(filename))
                {
                    using (StreamReader sr = new StreamReader(filename, Encoding.Default))
                    {
                        xaml = sr.ReadToEnd();
                    }
                    entity.Infomation = xaml;
                    File.Delete(filename);
                }
                
                if (entity.ID > 0)
                    return UpdatePublicOfficialInfo(entity, fs);
                string sql = string.Format(@"INSERT INTO [PublicOfficialInfo]
           ([Name]
           ,[Type]
           ,[Infomation]
           ,[DSC]
           ,[InputName]
           ,[InputTime]
           ,[DownloadFile]
           ,[Status]
           , Office
           , YM)
     VALUES
           (@Name
           ,@Type
           ,@Infomation
           ,@DSC
           ,@InputName
           ,@InputTime
           ,@DownloadFile
           ,@Status
            ,@Office
            , @YM)      select @@IDENTITY;");
                List<SqlParameter> parameters = new List<SqlParameter>();
                foreach (PropertyInfo pi in entity.GetType().GetProperties())
                {
                    if (pi.Name.Equals("ID") || pi.Name == "Orgs")
                        continue;
                    object value = pi.GetValue(entity, null);
                    parameters.Add(new SqlParameter("@" + pi.Name, value == null ? System.DBNull.Value : value));
                }
                int id = int.Parse(AccessHelper.GetInstance().ExcuteDataSet(sql, parameters.ToArray()).Tables[0].Rows[0][0].ToString());

                #region update PublicOfficialSelection
                string sqlUpdate = "update PublicOfficialSelection  set ExameID={2} where Status={1} and FaBuType='{0}'  and IsLocked=0";
                if (entity.Type == 1)
                {
                    sqlUpdate = string.Format(sqlUpdate, "选调交流", 4, id);
                }
                else
                {
                    sqlUpdate = string.Format(sqlUpdate, "竞争性选拔", 4, id);
                }
                AccessHelper.GetInstance().ExecuteNonQuery(sqlUpdate);
                #endregion

                string deleteSql = "delete ExameOrg where ExameID=" + id;
                AccessHelper.GetInstance().ExecuteNonQuery(deleteSql);
                if (entity.Orgs != null && entity.Orgs.Count > 0)
                {
                    string sql1 = @"INSERT INTO [ExameOrg]
           ([ExameID]
           ,[OrgForHRID])
     VALUES
           (@ExameID
           ,@OrgForHRID)";
                    SqlParameter[] sps = new SqlParameter[2];
                    foreach (var c in entity.Orgs)
                    {
                        sps[0] = new SqlParameter("@ExameID", id);
                        sps[1] = new SqlParameter("@OrgForHRID", c.OrgForHRID);
                        AccessHelper.GetInstance().ExecuteSql(sql1, sps);
                    }
                }
                string fileids = "(0";
                if (fs != null && fs.Length > 0)
                {
                    foreach (var c in fs)
                    {
                        c.ParentTableKey = id;
                        InsertFiles(c);
                        fileids += ", " + c.ID;
                    }
                }
                fileids += ")";
                DeleteFile(fileids, "file");
                sql = "delete [Files] where ParentTableKey = " + id + " and ID not in " + fileids;
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
                //if (count == 1)
                //    return true;
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        private void DeleteFile(string ids, string key)
        {
            string sql = "select [filename] from Files where ID not in " + ids;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            //string uploadFolder = System.Web.Hosting.HostingEnvironment.MapPath("~/" + path);
            string uploadFolder = System.Web.Configuration.WebConfigurationManager.AppSettings[key].ToString();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    string filename = dr[0] as string;
                    if (File.Exists(uploadFolder + @"\" + filename))
                        File.Delete(uploadFolder + @"\" + filename);
                }
            }
        }

        private void InsertFiles(Files f)
        {
            if (f.ID > 0)
            {
                UpdateFiles(f);
                return;
            }
            string sql = @"INSERT INTO [Files]
           ([ParentTableKey]
           ,[filename]
           ,[IsEnable]
           ,[DSC]
           ,[InputUserID]
           ,[InputDate]
           ,[Displayname])
     VALUES
           (@ParentTableKey
           ,@filename
           ,@IsEnable
           ,@DSC
           ,@InputUserID
           ,@InputDate
           ,@Displayname)  select @@IDENTITY;";
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pi in f.GetType().GetProperties())
            {
                if (pi.Name.Equals("ID"))
                    continue;
                object value = pi.GetValue(f, null);
                parameters.Add(new SqlParameter("@" + pi.Name, value == null ? System.DBNull.Value : value));
            }
            //AccessHelper.GetInstance().ExecuteNonQuery(sql);
            int id = int.Parse(AccessHelper.GetInstance().ExcuteDataSet(sql, parameters.ToArray()).Tables[0].Rows[0][0].ToString());
            f.ID = id;

        }

        private void UpdateFiles(Files f)
        {
            string sql = @"UPDATE [Files]
   SET [ParentTableKey] = @ParentTableKey
      ,[filename] = @filename
      ,[IsEnable] = @IsEnable
      ,[DSC] = @DSC
      ,[InputUserID] = @InputUserID
      ,[InputDate] = @InputDate
      ,[Displayname] = @Displayname
 WHERE ID= @ID";
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pi in f.GetType().GetProperties())
            {
                //if (pi.Name.Equals("ID"))
                //    continue;
                object value = pi.GetValue(f, null);
                parameters.Add(new SqlParameter("@" + pi.Name, value == null ? System.DBNull.Value : value));
            }
            AccessHelper.GetInstance().ExecuteSql(sql, parameters.ToArray());
        }

        public List<Files> GetFiles(int parentid)
        {
            List<Files> list = new List<Files>();
            string sql = @"select * from [Files]
where ParentTableKey = " + parentid;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Files f = new Files();
                PropertyInfo[] pis = f.GetType().GetProperties();
                foreach (PropertyInfo p in pis)
                {
                    object value = dr[p.Name];
                    if (value != null && value != System.DBNull.Value)
                        p.SetValue(f, value, null);
                }
                list.Add(f);
            }
            return list;

        }

        private bool UpdatePublicOfficialInfo(PublicOfficialInfo entity, Files[] fs)
        {
            string sql = string.Format(@"UPDATE [PublicOfficialInfo]
   SET [Name] = @Name
      ,[Type] = @Type
      ,[Infomation] = @Infomation
      ,[DSC] = @DSC
      ,[InputName] = @InputName
      ,[InputTime] = @InputTime
      ,[DownloadFile] = @DownloadFile
      ,[Status] = @Status
        ,Office=@Office
,YM=@YM 
 WHERE ID= @ID");
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pi in entity.GetType().GetProperties())
            {
                if (pi.Name == "Orgs")
                    continue;
                object value = pi.GetValue(entity, null);
                parameters.Add(new SqlParameter("@" + pi.Name, value == null ? System.DBNull.Value : value));
            }
            int count = AccessHelper.GetInstance().ExecuteSql(sql, parameters.ToArray());
            string fileids = "(0";
            if (fs != null && fs.Length > 0)
            {
                foreach (var c in fs)
                {
                    c.ParentTableKey = entity.ID;
                    InsertFiles(c);
                    fileids += ", " + c.ID;
                }
            }
            fileids += ")";
            #region update PublicOfficialSelection
            string sqlUpdate = "update PublicOfficialSelection  set ExameID={2} where Status={1} and FaBuType='{0}'  and IsLocked=0";
            if (entity.Type == 1)
            {
                sqlUpdate = string.Format(sqlUpdate, "选调交流", 4, entity.ID);
            }
            else
            {
                sqlUpdate = string.Format(sqlUpdate, "竞争性选拔", 4, entity.ID);
            }
            AccessHelper.GetInstance().ExecuteNonQuery(sqlUpdate);
            #endregion

            string deleteSql = "delete ExameOrg where ExameID=" + entity.ID;
            AccessHelper.GetInstance().ExecuteNonQuery(deleteSql);
            if (entity.Orgs != null && entity.Orgs.Count > 0)
            {
                string sql1 = @"INSERT INTO [ExameOrg]
           ([ExameID]
           ,[OrgForHRID])
     VALUES
           (@ExameID
           ,@OrgForHRID)";
                SqlParameter[] sps = new SqlParameter[2];
                foreach (var c in entity.Orgs)
                {
                    sps[0] = new SqlParameter("@ExameID", entity.ID);
                    sps[1] = new SqlParameter("@OrgForHRID", c.OrgForHRID);
                    AccessHelper.GetInstance().ExecuteSql(sql1, sps);
                }
            }
            DeleteFile(fileids, "file");
            sql = "delete [Files] where ParentTableKey = " + entity.ID + " and ID not in " + fileids;
            AccessHelper.GetInstance().ExecuteNonQuery(sql);
            //if (count == 1)
            //    return true;
            return true;
        }

        public bool DeletePublicOfficialInfo(int id)
        {
            string sql = "delete PublicOfficialInfo where id=" + id;

            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql);
            if (count == 1)
                return true;
            return false;
        }


        /// <summary>
        /// 获取分页
        /// </summary>
        /// <returns></returns>
        public int GetRegisterPositionPageCount(string condition, int pagesize)
        {
            ObservableCollection<RegisterPosition> collection = new ObservableCollection<RegisterPosition>();
            string sql = string.Format(@"select count(*) from RegisterPosition r
left join PublicOfficialSelection p on r.PositionID = p.ID
left join OrgForHR o on p.OrgID =o.ID");
            if (!string.IsNullOrEmpty(condition))
            {
                sql += "  where " + condition;
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            int count = (int)ds.Tables[0].Rows[0][0];
            int remain = count % pagesize;
            if (remain > 0)
                return count / pagesize + 1;
            else
                return count / pagesize;
        }


        public ObservableCollection<RegisterPosition> GetRegisterPosition(string condition, int pageindex, int pagesize)
        {
            ObservableCollection<RegisterPosition> collection = new ObservableCollection<RegisterPosition>();
            string sql = @"select top {2} * from (
select ROW_NUMBER() over(order by r.id) as [index], r.ID, p.ID as PositionID, r.EmpID, r.Name, r.CardNo, 
Isnull(r.[Status], 4) [Status], O.Name AS XXDW, p.Department, p.Duty, p.PeopleCount, 
p.Education, p.Political  from RegisterPosition r
left join PublicOfficialSelection p on r.PositionID = p.ID
left join OrgForHR o on p.OrgID =o.ID  where {0}) a
 where a.[index] > {2} * {1}";
            if (string.IsNullOrEmpty(condition))
            {
                condition = "1=1";
            }
            sql = string.Format(sql, condition, pageindex, pagesize);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    RegisterPosition entity = new RegisterPosition();
                    PropertyInfo[] pis = entity.GetType().GetProperties();
                    foreach (PropertyInfo pi in pis)
                    {
                        if (dr[pi.Name] != null && !System.DBNull.Value.Equals(dr[pi.Name]))
                        {
                            pi.SetValue(entity, dr[pi.Name], null);
                        }
                    }
                    collection.Add(entity);
                }
            }

            return collection;
        }

        public bool UpdateRegisterPosition(RegisterPosition entity)
        {
            string sql = @"UPDATE [RegisterPosition] set
	  [PositionID] = @PositionID
      ,[EmpID] = @EmpID
      ,[Name] = @Name
      ,[CardNo] = @CardNo
      ,[Status] = @Status
 WHERE ID = @ID";
            List<SqlParameter> listP = new List<SqlParameter>();
            List<string> list = new List<string>()
            {
                "ID",
      "PositionID",
      "EmpID",
      "Name",
      "CardNo",
      "Status",
            };
            foreach (string s in list)
            {
                PropertyInfo pi = entity.GetType().GetProperty(s);
                object value = pi.GetValue(entity, null);
                if (value == null)
                    value = System.DBNull.Value;
                listP.Add(new SqlParameter() { ParameterName = "@" + s, Value = value });
            }
            int count = AccessHelper.GetInstance().ExecuteSql(sql, listP.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        public Emp_BasicInfoRegister GetEmp_BasicInfoRegister(string name, string idcard, int positionid)
        {
            string sql = string.Format("select * from Emp_BasicInfoRegister where name='{0}' and CardNo={1}", name, idcard);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            Emp_BasicInfoRegister entity = null;
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    entity = new Emp_BasicInfoRegister();
                    PropertyInfo[] pis = entity.GetType().GetProperties();
                    foreach (PropertyInfo pi in pis)
                    {
                        object value = dr[pi.Name];
                        if (value != null && !System.DBNull.Value.Equals(value))
                        {
                            pi.SetValue(entity, value, null);
                        }
                    }
                }

            }
            else
            {
                entity = new Emp_BasicInfoRegister() { Name = name, ID = 0, CardNo = idcard, PositionID = positionid };
            }
            return entity;
        }


        public bool UploadFile(string key, string filename, byte[] content, bool Isfirst)
        {
            ///现在的路径，不行，为什么？  "d:\\XuhuiOfficialFiles\\files"
            string uploadFolder = System.Web.Configuration.WebConfigurationManager.AppSettings[key].ToString();
            //文件上传所在目录
            //原来的路径可以生成文件  "E:\\work\\徐汇\\svn2\\CommunitySystem\\CommunitySystem.Web\\file"
            //string uploadFolder = System.Web.Hosting.HostingEnvironment.MapPath("~/" + path);
            //目录不存在则新建
            if (!System.IO.Directory.Exists(uploadFolder))
            {
                System.IO.Directory.CreateDirectory(uploadFolder);
            }
            //文件读写模式
            System.IO.FileMode fileMode = System.IO.FileMode.Append;

            //第一次写入，如果有相同的文件，删除
            if (Isfirst && File.Exists(uploadFolder + @"\" + filename))
            {
                File.Delete(uploadFolder + @"\" + filename);

            }
            if (Isfirst)
                fileMode = System.IO.FileMode.Create;

            //写入文件
            using (System.IO.FileStream fs = new System.IO.FileStream(uploadFolder + @"\" + filename, fileMode, System.IO.FileAccess.Write))
            {
                fs.Write(content, 0, content.Length);
                fs.Close();

            }

            return true;
        }


        public ObservableCollection<PublicOfficialRecordModel> GetShenPi(int nian, string fabutype, string islocked, string orgids)
        {
            ObservableCollection<PublicOfficialRecordModel> rights = new ObservableCollection<PublicOfficialRecordModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY a.OrgID ) Sec, a.* from
(select distinct o.Name OrgName,a.XuQiuCount,pos.FaBuType,o.ID OrgID,pos.IsLocked,pos.Year from PublicOfficialRecord por inner join 
PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
inner join OrgForHR o on o.ID = pos.OrgID
left join 
(select COUNT(ID) XuQiuCount,OrgID from PublicOfficialSelection where Status = 4 group by OrgID) 
a on a.OrgID = o.ID ) a
where a.Year = {0} and 1=1", nian);
            if (!string.IsNullOrEmpty(fabutype)) sql += " and a.FaBuType = '" + fabutype + " '";
            if (!string.IsNullOrEmpty(islocked)) sql += " and a.IsLocked = '" + islocked + "'";
            if (!string.IsNullOrEmpty(orgids)) sql += " and a.OrgID in (" + orgids + ")";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PublicOfficialRecordModel.FillEntity2(dr));
            }
            return rights;
        }

        public bool UpdateShenPi(PublicOfficialRecordModel item)
        {
            string sql = string.Format(@"UPDATE PublicOfficialRecordModel set status = '{0}' where id = {1}", item.Status, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        #region 任前管理
        /// <summary>
        /// 用于任前管理--考察情况列表
        /// </summary>
        /// <param name="orgID"></param>
        /// <returns></returns>
        public ObservableCollection<RenQianKaoCha> GetKaoChaQingKuang(int orgID)
        {
            ObservableCollection<RenQianKaoCha> rights = new ObservableCollection<RenQianKaoCha>();
            string sql = "";

            sql =
                @"select ROW_NUMBER() OVER(ORDER BY r.ID ) Sec, emp.Name EmpName,pos.Duty ,r.Memo,por.ID PublicOfficialRecordID,r.ID from PublicOfficialSelection pos 
inner join PublicOfficialRecord por on por.PublicOfficialSelectionID = pos.ID
inner join Emp_BasicInfo emp on emp.ID = por.EmpID
left join renqiankaocha r on r.PublicOfficialRecordID = por.ID where IsLocked = 0 and pos.Status = 4 and por.GeRenStatus = 1 and por.IsTongYi = '拟同意' and pos.OrgID = " + orgID;
            //if(isGongShi == "是")
            //{
            //    sql += " left join renqiantaolun r on r.PublicOfficialRecordID = por.ID";
            //    sql += "where IsLocked = 0 and pos.Status = 4 and por.GeRenStatus = 1 and r.IsTongYi = '同意' and por.IsTongYi = '拟同意' and pos.OrgID = " + orgID;
            //}
            //else
            //{
            //    sql += "where IsLocked = 0 and pos.Status = 4 and por.GeRenStatus = 1 and por.IsTongYi = '拟同意' and pos.OrgID = " + orgID;
            //}


            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(RenQianKaoCha.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<RenQianTaoLun> GetRenQianTaoLun(int orgID)
        {
            ObservableCollection<RenQianTaoLun> rights = new ObservableCollection<RenQianTaoLun>();
            string sql = "";

            sql =
                @"select ROW_NUMBER() OVER(ORDER BY r.ID ) Sec, emp.Name EmpName,pos.Duty ,r.SNian,r.SYue,r.SRi,r.Memo,r.IsTongYi,por.ID PublicOfficialRecordID,r.ID from PublicOfficialSelection pos 
inner join PublicOfficialRecord por on por.PublicOfficialSelectionID = pos.ID
inner join Emp_BasicInfo emp on emp.ID = por.EmpID
left join RenQianTaoLun r on r.PublicOfficialRecordID = por.ID where IsLocked = 0 and pos.Status = 4 and por.GeRenStatus = 1 and por.IsTongYi = '拟同意' and pos.OrgID = " + orgID;

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(RenQianTaoLun.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<RenQianGongShi> GetRenQianGongShi(int orgID)
        {
            ObservableCollection<RenQianGongShi> rights = new ObservableCollection<RenQianGongShi>();
            string sql = "";

            sql =
                @"select ROW_NUMBER() OVER(ORDER BY r.ID ) Sec, emp.Name EmpName,pos.Duty,r.ID ,r.SNian,r.SYue,r.SRi,r.ENian,r.EYue,r.ERi,r.IsFanYing,r.ChuLiQingKuang,
por.ID PublicOfficialRecordID,por.IsShenQingZhuanRen,por.IsZhuanRen from PublicOfficialSelection pos 
inner join PublicOfficialRecord por on por.PublicOfficialSelectionID = pos.ID
inner join Emp_BasicInfo emp on emp.ID = por.EmpID
left join RenQianGongShi r on r.PublicOfficialRecordID = por.ID where IsLocked = 0 and pos.Status = 4 and por.GeRenStatus = 1 and por.IsTongYi = '拟同意' and pos.OrgID = " + orgID;

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(RenQianGongShi.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 插入考察情况
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool InsertRenQianKaoCha(RenQianKaoCha model)
        {
            string sql = string.Format(@"delete from RenQianKaoCha where id = {2};INSERT INTO RenQianKaoCha
           ([PublicOfficialRecordID]
           ,[Memo])
     VALUES
           ({0}
           ,'{1}')", model.PublicOfficialRecordID, model.Memo, model.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 插入讨论情况
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool InsertRenQianTaoLun(RenQianTaoLun model)
        {
            string sql = string.Format(@"delete from RenQianTaoLun where id = {0};INSERT INTO RenQianTaoLun
           ([PublicOfficialRecordID]
           ,[SNian]
           ,[SYue]
           ,[SRi]
           ,[Memo]
           ,[IsTongYi])
     VALUES
           ({1}
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", model.ID, model.PublicOfficialRecordID, model.SNian, model.SYUe, model.SRi, model.Memo, model.IsTongYi);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 插入公式情况
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool InsertRenQianGongShi(RenQianGongShi model)
        {
            string sql = string.Format(@"delete from RenQianGongShi where id = {0};INSERT INTO RenQianGongShi
           ([PublicOfficialRecordID]
           ,[SNian]
           ,[SYUe]
           ,[SRi]
           ,[ENian]
           ,[EYue]
           ,[ERi]
           ,[IsFanYing]
           ,[ChuLiQingKuang])
     VALUES
           ({1}
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}')", model.ID, model.PublicOfficialRecordID, model.SNian, model.SYUe, model.SRi, model.ENian, model.EYue, model.ERi, model.IsFanYing, model.ChuLiQingKuang);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool QueRenShenQingZhuanRen(string ids)
        {
            string[] ss = ids.Split(',');
            string yanzhengsql =
                @"select COUNT(a.Cou) Cou from 
(select COUNT(ID) Cou from RenQianGongShi where PublicOfficialRecordID in (" +
                ids + ")" +
                @"union all
select COUNT(ID) Cou from RenQianKaoCha where PublicOfficialRecordID in (" + ids + ")" +
                @"union all
select COUNT(ID) Cou from RenQianTaoLun where PublicOfficialRecordID in (" + ids + ")) a";

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(yanzhengsql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0 && Convert.ToInt32(ds.Tables[0].Rows[0][0]) == ss.Count() * 3)
            {
                string sql = "update PublicOfficialRecord set IsShenQingZhuanRen = 1 where ID in (" + ids + ")";
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return true;
            }
            else return false;
        }
        public bool QueRenZhuanRen(int id)
        {
            string sql = string.Format(@"update PublicOfficialRecord set IsZhuanRen = 1 where ID = {0};
update Emp_BasicInfo set OrgID = (
select pos.OrgID from PublicOfficialRecord por
inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
where por.ID = {0}) , PositionName = 
(select pos.Department_Duty from PublicOfficialRecord por
inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
where por.ID = {0})  where id = (select EmpID from PublicOfficialRecord where ID = {0});
update Emp_Duty set JobStatus = '不在任' where EmpbasicID = (select EmpID from PublicOfficialRecord where ID = {0});
INSERT INTO Emp_Duty
           ([EmpbasicID]
           ,[DutyNo]
           ,[DutyName]
           ,[DutyLevel]
           ,[DutyAttribute]
           ,[OKDate]
           ,[OrganizationName]
           ,[Sec]
           ,[WorkCharge]
           ,[ServiceMode]
           ,[IsExceptionalPromotion]
           ,[JobStatus]
           ,[JobDate]) 
select por.EmpID,'',pos.Duty,pos.DutyLevel,pos.DutyAttribute,GETDATE(),o.Name,'',
'','','','在任',GETDATE() from PublicOfficialRecord por
inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
inner join OrgForHR o on o.ID = pos.OrgID
where por.ID = {0};
INSERT INTO Emp_Resume
           ([EmpbasicID]
           ,[BeginDate]
           ,[Unit]
           ,[Duty])
select por.EmpID,GETDATE(),o.Name,pos.Duty from PublicOfficialRecord por
inner join PublicOfficialSelection pos on por.PublicOfficialSelectionID = pos.ID
inner join OrgForHR o on o.ID = pos.OrgID
where por.ID = {0};
INSERT INTO CheGai
           ([EmpID]
           ,[OldZhiJi]
           ,[Status]
           ,[Demo]
           ,[RelationID],KeyID,type,CreateDate)
     select  por.EmpID,DutyLevel,'未导出' ,'选拔工作',por.id,
case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动类' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = por.EmpID)) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动类' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = por.EmpID)) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,'变动类',getdate()  from 
     PublicOfficialRecord por inner join dbo.Emp_BasicInfo emp
     on por.EmpID = emp.id
left join Emp_Duty ed on emp.id = ed.EmpbasicID 
where [JobStatus] = '不在任' 
and ed.id=(select top 1 id from Emp_Duty where empbasicid = emp.id and [JobStatus] = '不在任'  order by id desc) 
and por.ID = {0}
     ", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 图片导入
        public Emp_BasicInfoModel[] ValidateHasPhoto(string[] idcards, string loginUser)
        {
            List<Emp_BasicInfoModel> list = new List<Emp_BasicInfoModel>();
            string sql = @"select name, cardno, hasphoto from emp_basicinfo
where lower(cardno) in ({0})";
            string condition = "'0'";
            foreach (string id in idcards)
            {
                condition += ",'" + id + "'";
            }
            sql = string.Format(sql, condition);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Emp_BasicInfoModel entity = new Emp_BasicInfoModel();
                    entity.Name = dr["name"] as string;
                    entity.CardNo = dr["cardno"] as string;
                    entity.HasPhoto = dr["hasphoto"] as bool?;
                    list.Add(entity);
                }
            }
            return list.ToArray();
        }

        public bool UpdateHasPoto(string cardNo, bool HasPhoto, string filename)
        {
            string sql = "update emp_basicinfo set hasphoto=@hasphoto, PhotoName=@photoname where CardNo=@cardno";
            SqlParameter[] ps = new SqlParameter[3];
            ps[0] = new SqlParameter("@hasphoto", HasPhoto);
            ps[1] = new SqlParameter("@cardno", cardNo);
            ps[2] = new SqlParameter("@photoname", filename);
            int count = AccessHelper.GetInstance().ExecuteSql(sql, ps);
            if (count == 1)
                return true;
            return false;
        }

        public DownLoadFile DownLoadFile(DownLoadFile file)
        {
            string uploadFolder = System.Web.Configuration.WebConfigurationManager.AppSettings[file.PathKey].ToString();
            if (File.Exists(uploadFolder + @"\" + file.FileName))
            {
                //if (info.start == 0)
                //{
                byte[] OneFile = null;
                OneFile = File.ReadAllBytes(uploadFolder + @"\" + file.FileName);
                //}
                if (file.Content == null)
                    file.Content = new byte[file.Lenght];
                if (OneFile.Length <= file.StartPosition + file.Lenght)
                    file.HasFinished = true;
                if (OneFile.Length < file.StartPosition + file.Lenght)
                {
                    file.Content = new byte[OneFile.Length - file.StartPosition];
                }
                for (int i = file.StartPosition; i < OneFile.Length && i < file.StartPosition + file.Lenght; i++)
                {
                    file.Content[i - file.StartPosition] = OneFile[i];
                }

                return file;
                //return new ObservableCollection<byte>(File.ReadAllBytes(uploadFolder + @"\" + info.Name));
            }
            else
                return null;
        }

        #endregion
        #region excel导出
        private byte[] _content = null;
        public int _id;
        public FileTransferStream GetExcelFile(ObservableCollection<PublicOfficialRecordModel> models, string username,Dictionary<string,bool> dic)
        {
            FileTransferStream ftfs = new FileTransferStream();
            if (_content != null)
            {
            }
            else
            {
                HSSFWorkbook workbook = null;


                MemoryStream ms = new MemoryStream(ResourceFile.mianshi);
                workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
                ms.Dispose();
                GetInfo(models, workbook,dic);

                string strFileName = AppDomain.CurrentDomain.BaseDirectory + username + ".xls";
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);

                }
                using (FileStream file = File.Open(strFileName, FileMode.Open))
                {
                    BinaryReader r = new BinaryReader(file);

                    r.BaseStream.Seek(0, SeekOrigin.Begin);    //将文件指针设置到文件开

                    _content = r.ReadBytes((int)r.BaseStream.Length);
                }
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }


                //{
                //    _content = new byte[file.Length];
                //    file.Write(_content, 0, _content.Length);
                //}

                ftfs.IsFinished = true;
                ftfs.FileContent = _content;

            }
            return ftfs;
        }
        private DataSet GetInfo(ObservableCollection<PublicOfficialRecordModel> models, HSSFWorkbook workbook,Dictionary<string,bool> dic)
        {
            HSSFSheet sheet1 = workbook.GetSheetAt(0);
            HSSFCellStyle style = workbook.CreateCellStyle();
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            #region 基本信息
            
                for (int i = 0; i < models.Count; i++)
                {
                    var row = sheet1.CreateRow(i + 3);
                    row.Height = 20 * 20;
                    for (int ii = 0; ii < 31; ii++)
                    {
                        HSSFCell cell = row.CreateCell(ii);
                        cell.CellStyle = style;
                    }
                    SetCellValue(sheet1, i + 4, 0, models[i].Sec.ToString());
                    SetCellValue(sheet1, i + 4, 1, models[i].RiQi != null ? Convert.ToDateTime(models[i].RiQi).ToString("yyyy-MM-dd") : null);
                    SetCellValue(sheet1, i + 4, 2, models[i].Zu);
                    SetCellValue(sheet1, i + 4, 3, models[i].Shi);
                    SetCellValue(sheet1, i + 4, 4, models[i].Department);
                    SetCellValue(sheet1, i + 4, 5, models[i].Duty);
                    SetCellValue(sheet1, i + 4, 6, models[i].EmpName);
                    SetCellValue(sheet1, i + 4, 7, models[i].BiShiScore.ToString());
                    SetCellValue(sheet1, i + 4, 8, models[i].CardNo);
                    SetCellValue(sheet1, i + 4, 9, models[i].OrgName);

                    SetCellValue(sheet1, i + 4, 10, models[i].PositionName);
                    SetCellValue(sheet1, i + 4, 11, models[i].DutyLevel);
                   
                    SetCellValue(sheet1, i + 4, 12, models[i].OKDate != null ? Convert.ToDateTime(models[i].OKDate).ToString("yyyy-MM-dd") : null);
                    SetCellValue(sheet1, i + 4, 13, models[i].DutyAttribute);
                    SetCellValue(sheet1, i + 4, 14, models[i].Gender);
                    SetCellValue(sheet1, i + 4, 15, models[i].Birthday != null ? Convert.ToDateTime(models[i].Birthday).ToString("yyyy-MM-dd") : null);
                    SetCellValue(sheet1, i + 4, 16, models[i].Political);
                    SetCellValue(sheet1, i + 4, 17, models[i].Native);

                    SetCellValue(sheet1, i + 4, 18, models[i].BenKeSchool);
                    SetCellValue(sheet1, i + 4, 19, models[i].BenKeProfessional);
                    SetCellValue(sheet1, i + 4, 20, models[i].ShuoShiSchool);
                    SetCellValue(sheet1, i + 4, 21, models[i].ShuoShiProfessional);
                    SetCellValue(sheet1, i + 4, 22, models[i].BoShiSchool);
                    SetCellValue(sheet1, i + 4, 23, models[i].BoShiProfessional);
                    SetCellValue(sheet1, i + 4, 24, models[i].FullEducationBg);
                    SetCellValue(sheet1, i + 4, 25, models[i].FullDegree);
                    SetCellValue(sheet1, i + 4, 26, models[i].Phone);
                    SetCellValue(sheet1, i + 4, 27, models[i].Email);
                    SetCellValue(sheet1, i + 4, 28, models[i].Health);
                    SetCellValue(sheet1, i + 4, 29, models[i].Marriage);
                    SetCellValue(sheet1, i + 4, 30, models[i].HomeAddress);
                }
           
            
            

            #endregion
            //hide column C
            if (dic["面试日期"] == false) sheet1.SetColumnHidden(1, true);
            if (dic["场次"] == false) sheet1.SetColumnHidden(2, true);
            if (dic["面试时间"] == false) sheet1.SetColumnHidden(3, true);
            if (dic["招录单位"] == false) sheet1.SetColumnHidden(4, true);
            if (dic["报考职位"] == false) sheet1.SetColumnHidden(5, true);
            if (dic["姓名"] == false) sheet1.SetColumnHidden(6, true);
            if (dic.Keys.Contains("笔试分数")) 
            {
                if (dic["笔试分数"] == false) 
                sheet1.SetColumnHidden(7, true);
            }
            else sheet1.SetColumnHidden(7, true);
            if (dic["身份证号"] == false) sheet1.SetColumnHidden(8, true);
            if (dic["现任单位"] == false) sheet1.SetColumnHidden(9, true);
            if (dic["现任职务"] == false) sheet1.SetColumnHidden(10, true);

            if (dic["现任职务层次"] == false) sheet1.SetColumnHidden(11, true);
            if (dic["现任职务层次时间"] == false) sheet1.SetColumnHidden(12, true);
            if (dic["职务属性"] == false) sheet1.SetColumnHidden(13, true);
            if (dic["性别"] == false) sheet1.SetColumnHidden(14, true);
            if (dic["出生日期"] == false) sheet1.SetColumnHidden(15, true);
            if (dic["政治面貌"] == false) sheet1.SetColumnHidden(16, true);
            if (dic["民族"] == false) sheet1.SetColumnHidden(17, true);
            if (dic["本科毕业院校"] == false) sheet1.SetColumnHidden(18, true);
            if (dic["本科专业"] == false) sheet1.SetColumnHidden(19, true);
            if (dic["硕士毕业院校"] == false) sheet1.SetColumnHidden(20, true);
            if (dic["硕士专业"] == false) sheet1.SetColumnHidden(21, true);

            if (dic["博士毕业院校"] == false) sheet1.SetColumnHidden(22, true);
            if (dic["博士专业"] == false) sheet1.SetColumnHidden(23, true);
            if (dic["学历"] == false) sheet1.SetColumnHidden(24, true);
            if (dic["学位"] == false) sheet1.SetColumnHidden(25, true);
            if (dic["联系方式/手机号码"] == false) sheet1.SetColumnHidden(26, true);
            if (dic["电子邮箱"] == false) sheet1.SetColumnHidden(27, true);
            if (dic["健康程度"] == false) sheet1.SetColumnHidden(28, true);
            if (dic["婚姻状况"] == false) sheet1.SetColumnHidden(29, true);
            if (dic["通讯地址"] == false) sheet1.SetColumnHidden(30, true);
            return null;
        }
        private bool SetCellValue(HSSFCell cell, object value)
        {
            if (value != null)
            {
                cell.SetCellValue(value.ToString());
            }
            return true;
        }
        private bool SetCellValue(HSSFSheet sheet, int row, int column, string dr)
        {
            HSSFRow hrow = sheet.GetRow(row - 1);
            if (hrow != null)
            {
                HSSFCell cell = hrow.GetCell(column);
                if (cell != null)
                {
                    SetCellValue(cell, dr);
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
            return true;
        }



        /// <summary>
        /// 判断是否已经有考试处于发布状态
        /// </summary>
        /// <param name="id">除该ID之外</param>
        /// <returns></returns>
        public bool ChechIsPublish(int id)
        {
            string sql = string.Format("select count(*) from PublicOfficialInfo where status =1 and id!={0}", id);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            int count = (int)ds.Tables[0].Rows[0][0];
            if (count == 0)
                return false;
            return true;
        }

        public bool UploadString(string tempFile, string content, bool IsFirst)
        {
            string uploadFolder = System.Web.Hosting.HostingEnvironment.MapPath("~/cash");
            if (!System.IO.Directory.Exists(uploadFolder))
            {
                System.IO.Directory.CreateDirectory(uploadFolder);
            }
            if (IsFirst)
            {
                if (IsFirst && File.Exists(uploadFolder + @"\" + tempFile))
                {
                    File.Delete(uploadFolder + @"\" + tempFile);
                }



            }
            using (StreamWriter sw = new StreamWriter(uploadFolder + @"\" + tempFile, !IsFirst, Encoding.Default))
            {
                sw.Write(content);
            }

            return true;
        }

        #endregion


    }
}
